Sorting Number Type

[keirnus]

Hello,

I am currently using Listview control.
The sorting feature of the control is useful.

<ListviewCtrlName>.Sorted = True
<ListviewCtrlName>.SortKey = <FieldToSort>
<ListviewCtrlName>.SortOrder = <SortOrder>

It sorts well with Text type but is weird when sorting Number Type.
Ex.
(before sorting)
[1st] 3
[2nd] 2
[3rd] 11
[4th] 4
[5th] 1

(after sorting)
[1st] 1
[2nd] 11
[3rd] 2
[4th] 3
[5th] 4

The correct one should be:
[1st] 1
[2nd] 2
[3rd] 3
[4th] 4
[5th] 11

How to sort number type correctly?

[ADezii]

@keirnus
If you set the SortKey Property = 0 it will Sort using the ListItem Object's Text property. Try setting it to a Value between 1 and Number of ColumnHeaders - 1 to Sort using the SubItem whose Collection Index you specify, then see what happens.

[keirnus]

@ADezii
(I edited this reply coz I was wrong...sorry)

Thanks for the reply ADezii.

But I noticed something. The SortKey is the column index in listview.
It starts with zero(0) so in my example, since there's only 1 column so
it starts with index 0. If I got 3 columns and want to sort the 2nd, I will
be using index 1. (please correct me if I'm wrong)

I tried sorting the column with number type and yet the sorting is wrong.
The sort is treating it like an alpha when it should be numeric.

[ADezii]

@keirnus
As soon as I get a chance, I'll create a DB with a ListView incorporated in a Form and see if I can 'Sort' the problem (LOL)!

[NeoPa]

It looks like you're simply sorting on the string value, instead of the numeric value.

[NeoPa]

@keirnus
Which values are you actually using?

[ADezii]

@keirnus
Keirnus, I do see your dilemma in that the ListView Control seems to always want to do an Alpha Sort on Column Headers regardless of the Data Type. I'll look into it further at a later Date, but I do have a solution at this time.

1. In the ColumnClick() Event of the ListView Control, if the Index of the ColumnHeader is anything but the 1st Column, set the SortKey = ColumnHeader.Index -1 then set the Sorted Property of the ListView = True.
2. If the Index of the ColumnHeader = Column 1, then:
   1. Clear the ListView Control
   2. Set the Sorted Property = False
   3. Create a RecordSet setting the Primary Sort Order of your Numeric Field to Ascending
   4. Re-populate the ListView based on this Recordset
   5. This entire process will be transparent to the User, and, unless you are dealing with very large Recordsets, will populate quite rapidly.
3. I created a Demo Database for you, simply download the Attachment, then Click on the Column Headers to set the Sorts for the 3 Columns

[NeoPa]

@keirnus
I'm confused.

I started by assuming you were referring to a ListBox control, but I didn't find any of these properties there so I stopped and looked again.

I found the ListView control, but even there, I found those properties missing.

I'm using Access 2003. Is it possible you're using a different version?

[ADezii]

@NeoPa
Hello NeoPa, these Properties definately exist for the ListView Control but not via the Normal Interface, you can access them either by:

1. Right Click the ListView Control ==> ListViewCtrl Object ==> Properties ==> Sorting Tab
2. Object Browser ==> Class = MSComctlLib ==> Search for ListView ==> Properties, Methods, and Events will appear in the Right Hand Pane

[missinglinq]

I can't lay my hands on a reference right now, but I'm about 99.9% sure that ListView controls only sort on strings and treat any other datatype as if they were strings when sorting, which is what it's doing here.

Linq ;0)>

[ADezii]

@missinglinq
I think you hit the nail on the Head, any attempt to Sort on Native Numeric Types, or Data Types coerced to Numeric, seem to fail. It appears as though the ListView must be repopulated as indicated in my prior Post.

[NeoPa]

@ADezii
Bizarre. It's almost as if I'm looking at a different, unrelated, ListView control.

Method 1 would be default for me. Didn't find anything (not even a sorting tab).
I also tried IntelliSense, but that yielded nothing either.

I'd like to see if I could find a way of coercing the item to a numeric value, but it's hard if I can't even find it.

Never mind.

[ADezii]

@NeoPa

1. Are we talking about the same animal, namely:
   

   Form Design View ==> Insert ==> ActiveX Control ==> Microsoft ListView Control, version X.X

2. Did you download the Attachment in Post #7 of this Thread, and is it functional?

[NeoPa]

1. I used the Toolbox button and More tools. If this is the same I don't know.
2. No. I rarely download attachments. I don't often have the time to get into questions that deeply, and I never download anything at my work PC.

PS. It's not important if I don't know too much about this control. I was only interested to see if I could help with the question. As you guys seem to be covering it anyway, it's hardly a problem if I'm on a rail track that is sort of parallel, but verges off on its way to a different city.